import express, { NextFunction, Request, Response } from 'express';
const router = express.Router();
import dbServe from '../db/db';

// 添加test表
router.get('/createTable', async function (req: Request, res: Response, next: NextFunction) {
  try {
    const columns = [
      { name: 'id', type: 'INTEGER PRIMARY KEY AUTOINCREMENT' },
      { name: 'name', type: 'TEXT' },
      { name: 'age', type: 'INTEGER' },
    ];
    let result = await dbServe.createTable('test', columns);
    if (result) {
      res.sendSuccess('表创建成功');
    } else {
      res.sendError(`创建表失败: ${result}`);
    }
  } catch (err) {
    next(err);
  }
});

// 插入一条数据
router.get('/insertData', async function (req: Request, res: Response, next: NextFunction) {
  try {
    const data = { name: 'John Doe', age: 25 };
    let result = await dbServe.insertData('test', data);
    if (result) {
      res.sendSuccess('数据插入成功');
    } else {
      res.sendError(`插入数据失败: ${result}`);
    }
  } catch (err) {
    next(err);
  }
});
// 更新一条数据
router.get('/updateData', async function (req: Request, res: Response, next: NextFunction) {
  try {
    const newData = { age: 30 };
    const condition = 'name = "John Doe"';
    let result = await dbServe.updateData('test', newData, condition);
    if (result) {
      res.sendSuccess('数据更新成功');
    } else {
      res.sendError(`更新数据失败: ${result}`);
    }
  } catch (err) {
    next(err);
  }
});

// 分页查询数据
router.get('/getPagedData', async function (req: Request, res: Response, next: NextFunction) {
  try {
    const page = 1;
    const pageSize = 10;
    const condition = 'age >= 30';
    let result = await dbServe.getPagedData('test', page, pageSize, condition);

    if (result) {
      res.sendSuccess('分页查询成功' + JSON.stringify(result));
    } else {
      res.sendError(`分页查询失败: ${result}`);
    }
  } catch (err) {
    next(err);
  }
});
// 删除一条数据
router.get('/deleteData', async function (req: Request, res: Response, next: NextFunction) {
  try {
    const deleteCondition = 'age >= 30';
    let result = await dbServe.deleteData('test', deleteCondition);
    if (result) {
      res.sendSuccess('数据删除成功');
    } else {
      res.sendError(`删除数据失败: ${result}`);
    }
  } catch (err) {
    next(err);
  }
});
// 删除表
router.get('/deleteTable', async function (req: Request, res: Response, next: NextFunction) {
  try {
    let result = await dbServe.deleteTable('test');
    if (result) {
      res.sendSuccess('表删除成功');
    } else {
      res.sendError(`删除表失败: ${result}`);
    }
  } catch (err) {
    next(err);
  }
});
// 导出路由器实例
export default router;
